﻿Imports MySql.Data.MySqlClient

Public Class add1_2

    Public SQL As MySqlConnection
    Public selectedEmployee As String
    Public selectedCourse As String


    Public Sub New(ByRef SQLConnection As MySqlConnection, ByRef inputSelectedEmployee As String)
        InitializeComponent()
        SQL = SQLConnection
        selectedEmployee = inputSelectedEmployee
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim nextForm As add1_1 = New add1_1(SQL)
        nextForm.Show()
        Me.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If selectedCourse = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else
            Dim nextForm As add1_3 = New add1_3(SQL, selectedEmployee, selectedCourse)
            nextForm.Show()
            Me.Close()
        End If
        
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim mainForm As main = New main()
        mainForm.Show()
        Me.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim detailForm As add1_2_1 = New add1_2_1(SQL, selectedEmployee)
        detailForm.Show()
        Me.Close()
    End Sub

    ' Private Sub add1_2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

    '    Dim mainForm As main = New main()
    '       mainForm.Show()
    'Me.Close()
    '  End Sub

    Private Sub add1_2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        selectedCourse = ""

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim maxyear As String
        Dim minyear As String
        Dim max() As String
        Dim min() As String
        Dim maxyearint As Integer
        Dim minyearint As Integer

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM Employee where emp_code =" + selectedEmployee + ";"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        While (mySqlReader.Read())

            Textname.Text = mySqlReader("emp_gender") + " " + mySqlReader("emp_name") + " " + mySqlReader("emp_surname")
            Textcode.Text = mySqlReader("emp_code")
            Textlevel.Text = mySqlReader("emp_level")
            Textposition.Text = mySqlReader("emp_position")

        End While

        SQL.Close()


        ComboBox1.Items.Clear()

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM course  order by course_start_date DESC limit 1;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())

            maxyear = mySqlReader("course_start_date")
            max = maxyear.Split("/")
            maxyearint = max(2)

        End While

        SQL.Close()

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM course  order by course_start_date ASC limit 1;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())

            minyear = mySqlReader("course_start_date")
            min = minyear.Split("/")
            minyearint = min(2)
        End While


        For i As Integer = minyearint To maxyearint Step 1

            If i <> Date.Now.Year And i <> 0 Then
                ComboBox1.Items.Add(i)
            End If


        Next i

        ComboBox1.Items.Add("ALL")

        SQL.Close()


        ComboBox1.Items.Add(Date.Now.Year + 543)
        ComboBox1.Text = Date.Now.Year + 543

        showdata()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        showdata()
    End Sub


    Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        selectedCourse = ListView1.SelectedItems(0).Text
    End Sub

    Private Sub showdata()
        Dim key As String
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader

        Dim keydate As String
        '---------------------------------------------Check Start date couse---------------------------
        keydate = ComboBox1.SelectedItem.ToString

        If keydate = "ALL" Then
            keydate = ""

        Else

            keydate = keydate - 543
        End If

        '---------------------------------------------End Check Start date couse---------------------------

        key = TextBox1.Text


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM Course  WHERE NOT course_id in (SELECT  course_id  FROM Training WHERE emp_code =" + selectedEmployee + ") and (course_id like" + "'%" + key + "%'or course_name like" + "'%" + key + "%'or course_trainer like '%" + key + "%')and (course_start_date like '%" + keydate + "%') order by course_name"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        Dim split_start() As String
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListView1.Items.Clear()

            While (mySqlReader.Read())


                With ListView1.Items.Add(mySqlReader("course_id"))
                    .subItems.add(mySqlReader("course_name"))

                    split_start = Split(mySqlReader("course_start_date"), "/")

                    .subItems.add(split_start(0) + "/" + split_start(1) + "/" + split_start(2))
                    .subItems.add(mySqlReader("course_trainer"))
                End With


            End While
        Catch
        End Try
        SQL.Close()
        'TextBox1.Text = ""
    End Sub

    Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown

        If e.KeyCode = "13" Then
            showdata()
        End If

    End Sub

    Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
        If selectedCourse = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else
            Dim nextForm As add1_3 = New add1_3(SQL, selectedEmployee, selectedCourse)
            nextForm.Show()
            Me.Close()
        End If
    End Sub

    Private Sub Textname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Textname.TextChanged

    End Sub

    Private Sub Label7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label7.Click

    End Sub

    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub

    Private Sub Deletedata()
        Dim sqlstat As String
        Dim cmd As New MySqlCommand
        Dim keyDel As String
        Dim respone As Object
        keyDel = selectedCourse

        respone = MsgBox("Are you sure to Delete data", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then

            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If

            '-----------------------------------Delete Couse--------------------------
            sqlstat = "DELETE FROM Course WHERE course_id ='" + keyDel + "'"
            With cmd
                .CommandText = sqlstat
                .CommandType = CommandType.Text
                .Connection = SQL
                .ExecuteNonQuery()

            End With

            SQL.Close()

            '----------------------------------Delete traning---------------------------

            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If

            sqlstat = "DELETE FROM Training WHERE course_id ='" + keyDel + "'"
            With cmd
                .CommandText = sqlstat
                .CommandType = CommandType.Text
                .Connection = SQL
                .ExecuteNonQuery()

            End With
            SQL.Close()

            respone = MsgBox("Delete Success", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Warning Message")

            Showdata()
        Else
            Exit Sub
        End If
    End Sub

    Private Sub ButtonDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonDelete.Click
        If selectedCourse = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else

            Deletedata()
        End If
    End Sub
End Class